package com.lhcz.helper;

import com.lhcz.jfinal.template.Template;
import com.lhcz.jfinal.template.ext.spring.JFinalViewResolver;
import com.lhcz.utils.CamelCaseUtils;
import com.lhcz.utils.FileUtils;
import com.lhcz.utils.PageUtil;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.hibernate.QueryException;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * @author hdy
 * @Description: jdbc
 * @date 2020/4/28 15:48
 */
//@Component
public class JdbcHelper {

    private final JFinalViewResolver jFinalViewResolver;
    private final JdbcTemplate jdbcTemplate;

    public JdbcHelper(JFinalViewResolver jFinalViewResolver, JdbcTemplate jdbcTemplate) {
        this.jFinalViewResolver = jFinalViewResolver;
        this.jdbcTemplate = jdbcTemplate;
    }


    /**
     * 获取list列表数据
     *
     * @param sqlFileName 文件名称
     * @return /
     */
    public List<Map<String, Object>> getSqlList(String sqlFileName) {
        return getSqlList(sqlFileName, null);
    }

    /**
     * 获取list列表数据
     *
     * @param sqlFileName 文件名称
     * @param data        参数
     * @return /
     */
    public List<Map<String, Object>> getSqlList(String sqlFileName, Map<String, Object> data) {
        String path = FileUtils.getSql(sqlFileName).replaceAll("\n\n", "");
        Template template = jFinalViewResolver.getEngine().getTemplateByString(path);
        String sql = template.renderToString(data);
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * 获取统计数据
     *
     * @param sqlFileName 文件名称
     * @return /
     */
    public int counts(String sqlFileName) {
        return counts(sqlFileName, null);
    }


    /**
     * 获取统计数据
     *
     * @param sqlFileName 文件名称
     * @param data
     * @return /
     */
    public int counts(String sqlFileName, Map<String, Object> data) {
        String path = FileUtils.getSql(sqlFileName);
        System.out.println("path = " + path);
        Template template = jFinalViewResolver.getEngine().getTemplateByString(path);
        String sql = template.renderToString(data).replaceAll("\n\n", "");
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
        return integer != null ? integer : 0;
    }

    /**
     * 分页列表 查询
     *
     * @param sqlFileName 文件名称
     * @param data        参数
     * @param pageable    分页  page=0 & size=3
     * @return /
     */
    public <T> Map<String, Object> queryAll(String sqlFileName, Map<String, Object> data, Class<T> clazz, Pageable pageable) {
        String path = FileUtils.getSql(sqlFileName);
        String sql = jFinalViewResolver.getEngine().getTemplateByString(path).renderToString(data);
        // 计算总页数
        String sqlCounts = "select COUNT(1) counts FROM ( " + sql + " ) a";
        String sqlQuery = PageUtil.toPageSql(sql, pageable.getPageNumber(), pageable.getPageSize());
        List<T> list = queryForList(sqlQuery, clazz);
        long total = queryOneColumn(sqlCounts, Long.class);
        return PageUtil.toPage(list, total);
    }

    /**
     * 分页列表 查询  注意：这种查询方式返回的为数据库表字段，时间精度需要修改，可以转一次对象解决
     *
     * @param sqlFileName 文件名称
     * @param data        参数
     * @param pageable    分页  page=0 & size=3
     * @return /
     */
    public Map<String, Object> queryObject(String sqlFileName, Map<String, Object> data, Pageable pageable) {
        String path = FileUtils.getSql(sqlFileName);
        String sql = jFinalViewResolver.getEngine().getTemplateByString(path).renderToString(data);
        // 计算总页数
        String sqlCounts = "select COUNT(1) counts FROM ( " + sql + " ) a";
        String sqlQuery = PageUtil.toPageSql(sql, pageable.getPageNumber(), pageable.getPageSize());
        List list = jdbcTemplate.queryForList(sqlQuery);
        long total = queryOneColumn(sqlCounts, Long.class);
        return PageUtil.toPage(list, total);
    }

    /**
     * 在count时，返回一个值的，将结果映射成一个值。
     *
     * @param sql    sql
     * @param clazz  clazz
     * @param params params
     * @param <T>    t
     * @return /
     */
    public <T> T queryOneColumn(String sql, Class<T> clazz, Object... params) {
        T result;
        if (ArrayUtils.isEmpty(params)) {
            result = jdbcTemplate.queryForObject(sql, clazz);
        } else {
            result = jdbcTemplate.queryForObject(sql, params, clazz);
        }
        return result;
    }

    /**
     * 返回实体对象
     *
     * @param sql    sql
     * @param clazz  clazz
     * @param params params
     * @param <T>    T
     * @return /
     */
    public <T> List<T> queryForList(String sql, Class<T> clazz, Object... params) {
        final List<T> result = new ArrayList<>();
        jdbcTemplate.query(sql, params, rs -> {
            try {
                // 字段名称
                List<String> columnNames = new ArrayList<>();
                ResultSetMetaData meta = rs.getMetaData();
                int num = meta.getColumnCount();
                for (int i = 0; i < num; i++) {
                    columnNames.add(meta.getColumnLabel(i + 1));
                }
                // 设置值
                do {
                    T obj = clazz.getConstructor().newInstance();
                    for (int i = 0; i < num; i++) {
                        // 获取值
                        Object value = rs.getObject(i + 1);
                        // table.column形式的字段去掉前缀table.
                        String columnName = resolveColumn(columnNames.get(i));
                        // 下划线转驼峰
                        String property = CamelCaseUtils.toCamelCase(columnName);
                        // 复制值到属性，这是spring的工具类
                        BeanUtils.copyProperty(obj, property, value);
                    }
                    result.add(obj);
                } while (rs.next());
            } catch (Exception e) {
                throw new QueryException(e);
            }
        });
        if (CollectionUtils.isEmpty(result)) {
            return Collections.emptyList();
        }
        return result;
    }

    /**
     * 去掉表前缀
     * 之所以去掉表前缀，是为了避免在SQL中使用别名，导致SQL过长
     *
     * @param column /
     * @return /
     */
    private String resolveColumn(String column) {
        final int notExistIndex = -1;
        int index = column.indexOf(".");
        if (index == notExistIndex) {
            return column;
        }
        return column.substring(index + 1);
    }
}
